-- 使用tedu数据库
USE tedu;

-- 选择教师表中的平均工资和职称，按职称分组
SELECT AVG(salary) 平均工资, title
FROM teacher
GROUP BY title;

-- 选择学生表中的班级ID和对应的人数，按班级ID分组
SELECT COUNT(*) 人数, class_id
FROM student
GROUP BY class_id;

-- 选择学生表中的职业、人数、最早的生日和最晚的生日，按职业分组
SELECT job 职位, COUNT(*) 人数, MAX(birth) 最小生日, MIN(birth) 最大生日
FROM student
GROUP BY job;

-- 选择学生表中的班级ID、性别和对应的人数，按班级ID和性别分组
SELECT COUNT(*) 人数, class_id, gender
FROM student
GROUP BY class_id, gender;

-- 选择学生表中的职业、班级ID和对应的人数，按职业和班级ID分组
SELECT COUNT(*) 人数, job 职位, class_id 班级
FROM student
GROUP BY job, class_id;

-- 选择学生表和班级表中的职业、班级名称和对应的人数，按职业和班级ID分组
SELECT COUNT(*) 人数, s.job 职位, c.name 班级名称
FROM student s
         JOIN class c ON s.class_id = c.id
WHERE s.class_id = c.id
GROUP BY job, class_id;

-- 选择教师表中的平均薪资和科目ID，按科目ID分组，并按平均薪资降序排序
SELECT AVG(salary) 平均薪资, subject_id
FROM teacher
GROUP BY subject_id
ORDER BY AVG(salary) DESC;

-- 选择平均薪资和科目ID，从教师表中按科目ID分组，筛选出平均薪资大于6000的记录
SELECT AVG(salary) 平均薪资, subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary) > 6000;

-- 选择平均薪资和科目ID，从教师表中按科目ID分组，筛选出最高薪资大于9000的记录
SELECT AVG(salary) 平均薪资, subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary) > 9000;

-- 选择最高工资和科目ID，从教师表中按科目ID分组，筛选出平均薪资大于6000的记录
SELECT MAX(salary) 最高工资, subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary) > 6000;

-- 选择姓名和薪资，从教师表中筛选出薪资高于王克晶的记录
SELECT name, salary
FROM teacher
WHERE salary > (SELECT salary FROM teacher WHERE name = '王克晶');

-- 选择工资高于教师平均工资的教师的姓名和工资
SELECT name, salary
FROM teacher
WHERE salary > (SELECT AVG(salary) FROM teacher);

-- 选择班级ID与名为'李费水'的学生相同的其他学生的姓名
SELECT name
FROM student
WHERE class_id = (SELECT class_id FROM student WHERE name = '李费水');

-- 选择所在楼层与'3年级2班'相同的班级的名称
SELECT name
FROM class
WHERE floor = (SELECT floor FROM class WHERE name = '3年级2班');

-- 选择工资等于教师中最高工资的教师的姓名、工资和佣金
SELECT name, salary, comm
FROM teacher
WHERE salary = (SELECT MAX(salary) FROM teacher);

-- 选择班级ID与名为'3年级2班'的班级ID相同的学生的姓名和班级ID
SELECT name, class_id
FROM student
WHERE class_id = (SELECT id FROM class WHERE name = '3年级2班');

-- 选择教授'语文'科目的教师的姓名和科目ID
SELECT name, subject_id
FROM teacher
WHERE subject_id = (SELECT id FROM subject WHERE name = '语文');
-- 选择祝雷和李费水所在班级的所有学生
SELECT name, age, class_id
FROM student
WHERE class_id IN (SELECT class_id FROM student WHERE name IN ('祝雷', '李费水'));

-- 选择工资高于科目ID为2和4的老师工资的所有老师
SELECT name, salary
FROM teacher
WHERE salary > ALL (SELECT salary FROM teacher WHERE subject_id IN (2, 4));

-- 减少与范传奇相同科目ID老师的工资500元
UPDATE teacher
SET salary = salary - 500
WHERE subject_id = (SELECT subject_id FROM teacher WHERE name = '范传奇');
-- 范传奇初始salary:3000

-- 选择所有老师的名字和工资
SELECT name, salary
FROM teacher;

-- 将王克晶的工资设置为与范传奇相同
UPDATE teacher
SET salary = (SELECT salary FROM teacher WHERE name = '范传奇')
WHERE name = '王克晶';

-- 将王克晶的工资设置为8000元
UPDATE teacher
SET salary = 8000
WHERE name = '王克晶';

-- 开始一个事务
BEGIN;
-- 从teacher表中删除名为'范传奇'的记录
DELETE
FROM teacher
WHERE name = '范传奇';
-- 选择并显示teacher表中的所有记录
SELECT *
FROM teacher;
-- 回滚事务，撤销对数据库的所有更改
ROLLBACK;
-- 插入一个新的教师记录到teacher表中
INSERT INTO teacher
VALUES (2, '范传奇', 33, '三级讲师', 1, 3000, NULL, '男', 2);

-- 选择教师姓名、科目姓名和教师工资，从教师和科目表中，当教师的科目ID等于科目表的ID时
SELECT teacher.name, subject.name, teacher.salary
FROM teacher,
     subject
WHERE teacher.subject_id = subject.id;

-- 选择班级名称和教师名称，从教师和班级表中，当班级的教师ID等于教师表的ID时
SELECT c.name, t.name
FROM teacher t,
     class c
WHERE c.teacher_id = t.id;

-- 选择学生姓名、班级楼层、班级名称和班级楼层，从学生和班级表中，当学生的班级ID等于班级表的ID时
SELECT s.name, c.floor, c.name, c.floor
FROM student s,
     class c
WHERE s.class_id = c.id;

-- 选择学生姓名、学生性别、学生年龄和地点名称，从地点和学生表中，当地点ID等于学生的地点ID时
SELECT s.name, s.gender, s.age, l.name
FROM location l,
     student s
WHERE l.id = s.location_id;
-- 选择老师和班级的名字，通过老师ID连接老师和班级表，筛选出老师名为王克晶的记录
SELECT t.name, c.name
FROM teacher t,
     class c
WHERE t.id = c.teacher_id
  AND t.name = '王克晶';

-- 选择学生和地点的名字、性别和年龄，通过地点ID连接学生和地点表，筛选出地点名为南京的记录
SELECT s.name, l.name, s.gender, s.age
FROM location l,
     student s
WHERE l.id = s.location_id
  AND l.name = '南京';

-- 选择老师和班级的名字、老师工资和班级楼层，通过老师ID连接老师和班级表，筛选出班级名包含3年级的记录
SELECT t.name, t.salary, c.name, c.floor
FROM teacher t,
     class c
WHERE t.id = c.teacher_id
  AND c.name LIKE '3年级%';

-- 选择学生的名字、年龄、性别、职务以及班级的名字和楼层，通过班级ID连接学生和班级表，筛选出班级名包含5年级且学生职务为中队长的记录
SELECT s.name, s.age, s.gender, s.job, c.name, c.floor
FROM student s,
     class c
WHERE s.class_id = c.id
  AND c.name LIKE '5年级%'
  AND s.job = '中队长';

-- 选择学生和老师的名字、学生的年龄以及班级的名字，通过班级ID和老师ID连接学生、老师和班级表，筛选出老师名为范传奇的记录
SELECT s.name, s.age, c.name, t.name
FROM student s,
     teacher t,
     class c
WHERE s.class_id = c.id
  AND t.id = c.teacher_id
  AND t.name = '范传奇';

-- 查询学生的名字、班级的名字和地点的名字。 通过班级ID和地点ID连接学生、班级和地点表，筛选出班级名为“1年级1班”的记录。
SELECT s.name, c.name, l.name
FROM student s,
     class c,
     location l
WHERE s.class_id = c.id
  AND s.location_id = l.id
  AND c.name = '1年级1班';


/**
 * 查询功能集合：以下SQL查询语句实现了多个与学生、教师、班级、科目和地点相关的数据检索功能。
 * 每个查询都针对特定的业务需求，涉及多表联结和条件过滤。
 */
-- 查询1：获取位于北京的学生及其对应的地点名称
SELECT s.name, l.name
FROM location l,
     student s
WHERE s.location_id = l.id
  AND l.name = '北京';

-- 查询2：获取教授英语的教师及其对应的科目名称
SELECT t.name, s.name
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
  AND s.name = '英语';

-- 查询3：获取教师“刘苍松”所教班级的学生姓名及班级名称
SELECT t.name, s.name, c.name
FROM teacher t,
     class c,
     student s
WHERE s.class_id = c.id
  AND c.teacher_id = t.id
  AND t.name = '刘苍松';

-- 查询4：获取教授语文的教师、其教授的班级及科目名称
SELECT t.name, s.name, c.name
FROM subject s,
     teacher t,
     class c
WHERE s.id = t.subject_id
  AND c.teacher_id = t.id
  AND s.name = '语文';

-- 查询5：获取教师“王克晶”所教学生的地点名称（去重）
SELECT DISTINCT l.name
FROM teacher t,
     location l,
     student s,
     class c
WHERE s.location_id = l.id
  AND s.class_id = c.id
  AND t.id = c.teacher_id
  AND t.name = '王克晶';

-- 查询6：获取班级名称以“3年级”开头的教师、班级及科目名称
SELECT t.name, c.name, s.name
FROM class c,
     teacher t,
     subject s
WHERE t.subject_id = s.id
  AND c.teacher_id = t.id
  AND c.name LIKE '3年级%';

-- 查询7：获取薪资大于10000的教师及其所教班级中担任“大队长”的学生信息
SELECT t.name, t.salary, s.name, s.job
FROM teacher t,
     class c,
     student s
WHERE s.class_id = c.id
  AND t.id = c.teacher_id
  AND t.salary > 10000
  AND s.job = '大队长';

-- 查询8：获取学生“李费水”所在班级的教师、科目及学生信息
SELECT st.name, t.name, su.name
FROM teacher t,
     class c,
     student st,
     subject su
WHERE st.class_id = c.id
  AND t.id = c.teacher_id
  AND su.id = t.subject_id
  AND st.name = '李费水';

-- 查询9：获取位于4楼的班级中担任“大队长”或“中队长”的学生及其教师信息
SELECT s.name, t.name, s.job, c.floor
FROM class c,
     teacher t,
     student s
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND c.floor = 4
  AND s.job IN ('大队长', '中队长');

-- 查询10：获取出生日期最晚的学生及其所在班级和教师信息
SELECT s.name, s.birth, t.name
FROM class c,
     teacher t,
     student s
WHERE c.teacher_id = t.id
  AND s.class_id = c.id
  AND s.birth = (SELECT MAX(birth) FROM student);

-- 查询11：获取教授语文的教师、其教授的班级、学生及学生所在地点的信息
SELECT su.name, t.name, c.name, st.name, l.name
FROM student st,
     teacher t,
     location l,
     subject su,
     class c
WHERE c.teacher_id = t.id
  AND c.id = st.class_id
  AND t.subject_id = su.id
  AND st.location_id = l.id
  AND su.name = '语文';

-- 查询12：获取出生日期最晚的学生及其所在班级、教师和地点的信息
SELECT s.name, s.birth, c.name, t.name, l.name
FROM student s,
     teacher t,
     location l,
     class c
WHERE s.location_id = l.id
  AND c.id = s.class_id
  AND t.id = c.teacher_id
  AND s.birth = (SELECT MAX(birth) FROM student);

-- 查询13：获取位于北京且薪资最高的教师及其所教班级和学生的信息
SELECT t.name, t.salary, c.name, l.name, s.name
FROM teacher t,
     class c,
     location l,
     student s
WHERE s.location_id = l.id
  AND c.id = s.class_id
  AND t.id = c.teacher_id
  AND l.name = '北京'
  AND t.salary = (SELECT MAX(salary) FROM teacher);

-- 查询14：统计教师“范传奇”所教授的学生人数及其姓名
SELECT COUNT(*) 人数, t.name
FROM teacher t,
     class c,
     student s
WHERE t.id = c.teacher_id
  AND c.id = s.class_id
  AND t.name = '范传奇';

-- 查询教师表中教授“语文”学科的教师的平均工资
SELECT s.name, AVG(t.salary) 平均工资
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
  AND s.name = '语文';

-- 查询教师表中每门学科的教师的平均工资
SELECT s.name, AVG(t.salary) 平均工资
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
GROUP BY s.name;

-- 查询教师表中学科的教师的平均工资大于6000的科目的教师平均工资
SELECT s.name, AVG(t.salary) 平均工资
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
GROUP BY s.name
HAVING AVG(t.salary) > 6000;

-- 查看工资最高的老师所带的学生有多少人
SELECT COUNT(*) 人数
FROM teacher t,
     class c,
     student s
WHERE t.id = c.teacher_id
  AND c.id = s.class_id
  AND t.salary = (SELECT MAX(salary) FROM teacher);


#--------------------------------------------------homework-------------------------------------------------------------
-- 1:查看科目老师的工资总和是多少?前提是该科老师的平均奖金要高于4000.
SELECT s.name, SUM(t.salary) 工资总和
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
GROUP BY s.name
HAVING AVG(t.salary) > 4000;

-- 2:查看各科目男老师的平均工资是多少?前提是该科目老师最低工资高于4000.
SELECT s.name, AVG(t.salary) 平均工资
FROM subject s,
     teacher t
WHERE s.id = t.subject_id
GROUP BY s.name
HAVING MIN(t.salary) > 4000;

-- 3:查看班级编号小于6的每个班各多少人?
SELECT class_id, COUNT(*) 人数
FROM student
WHERE class_id < 6
GROUP BY class_id;

-- 4:查看3层一共多少个班?
SELECT floor, COUNT(*) 班级数
FROM class
WHERE floor = 3;

-- 5:查看工资低于8000的老师的平均工资是多少?
SELECT AVG(salary) 平均工资
FROM teacher
WHERE salary < 8000;

-- 6:查看班级人数超过60人的班级中年纪最大的同学生日是多少?
SELECT c.name, MIN(s.birth) 生日最大, COUNT(*) 人数
FROM student s,
     class c
WHERE s.class_id = c.id
GROUP BY c.name
HAVING COUNT(*) > 60;

-- 7:查看教课程编号1的老师的平均年龄是多少?
SELECT subject_id, AVG(age) 平均年龄
FROM teacher
WHERE subject_id = 1;

-- 8:查看同一科目平均年龄超过35岁的老师中最小年龄是多少?
SELECT s.name, MIN(age) 最小年龄
FROM teacher t,
     subject s
WHERE t.subject_id = s.id
GROUP BY s.name
HAVING AVG(age) > 35;

-- 9:查看同职称人数超过4人的老师的平均工资是多少?
SELECT title, AVG(salary) 平均工资, COUNT(*) 人数
FROM teacher
GROUP BY title
HAVING COUNT(*) > 4;

-- 10:来自上海的学生的班主任都有谁?
SELECT DISTINCT t.name
FROM teacher t,
     student s,
     location l,
     class c
WHERE t.id = c.teacher_id
  AND l.id = s.location_id
  AND s.class_id = c.id
  AND l.name = '上海';

-- 11:来自南京的学生共多少人
SELECT l.name, COUNT(*) 人数
FROM location l,
     student s
WHERE s.location_id = l.id
  AND l.name = '南京';

-- 12:来自武汉的男同学和女同学分别多少人?
SELECT gender, COUNT(*) 人数
FROM student s,
     location l
WHERE s.location_id = l.id
  AND l.name = '武汉'
GROUP BY gender;

-- 13:每个城市的学生各多少人
SELECT l.name, COUNT(*) 人数
FROM student s,
     location l
WHERE s.location_id = l.id
GROUP BY l.name;

-- 14:高于平均工资的老师所带的班级分别多少学生?
SELECT c.name, COUNT(*) 人数
FROM teacher t,
     student s,
     class c
WHERE t.id = c.teacher_id
  AND s.class_id = c.id
  AND t.salary > (SELECT AVG(salary) FROM teacher)
GROUP BY c.name;

-- 15:每个老师班里各多少来自郑州的学生?
SELECT t.name, c.name, COUNT(*) 人数
FROM teacher t,
     class c,
     location l,
     student s
WHERE s.class_id = c.id
  AND s.location_id = l.id
  AND t.id = c.teacher_id
  AND l.name = '郑州'
GROUP BY t.name, c.name;








